﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace Sector7.Framework.Core
{
    /// <summary>
    /// ExcelDocument
    /// </summary>
    public class ExcelDocument
    {
        #region Inner members
       
        /// <summary>
        /// File Path
        /// </summary>
        private string m_filePath = "";

        /// <summary>
        /// Sheet Names
        /// </summary>
        private string[] m_sheetNames;

        /// <summary>
        /// Excel Application
        /// </summary>
        private Microsoft.Office.Interop.Excel._Application m_excelApplication;

        /// <summary>
        /// Excel Docuemnt
        /// </summary>
        private Workbook m_excelDoc;

        /// <summary>
        /// Excel Sheets
        /// </summary>
        private List<ExcelSheet> m_excelSheets = new List<ExcelSheet>();
        #endregion

        #region Accessor
        #endregion

        #region Structure

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="filePath">File Path</param>
        /// <param name="sheetNames">Sheet Names</param>
        public ExcelDocument(string filePath, params string[] sheetNames)
        {
            m_filePath = filePath;
            m_sheetNames = sheetNames;
        }

        #endregion

        #region Public functions

        /// <summary>
        /// Get Excel Sheet List
        /// </summary>
        /// <returns></returns>
        public List<ExcelSheet> GetExcelSheetList()
        {
            List<ExcelSheet> excelSheetList = new List<ExcelSheet>();
            if (Open())
            {
                Sheets sheets = m_excelDoc.Worksheets;

                List<Worksheet> workSheetsList = new List<Worksheet>();
                foreach (Worksheet sheet in sheets)
                {
                    if (m_sheetNames != null && m_sheetNames.Length != 0)
                    {
                        foreach (string sheetName in m_sheetNames)
                        {
                            if (sheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase))
                            {
                                workSheetsList.Add(sheet);
                                break;
                            }
                        }
                    }
                    else
                    {
                        workSheetsList.Add(sheet);
                    }
                }

                foreach (Worksheet sheet in workSheetsList)
                {
                    Range usedRange = sheet.UsedRange;

                    int rows = rows = usedRange.Rows.Count;
                    int columns = columns = usedRange.Columns.Count;
                    object[,] cells = sheet.UsedRange.get_Value(Missing.Value) as object[,];
                    if (cells == null)
                    {
                        rows = 0;
                        columns = 0;
                    }

                    ExcelSheet excelSheet = new ExcelSheet(sheet.Name, rows, columns);
                    excelSheetList.Add(excelSheet);

                    if (cells != null && cells.Length != 0)
                    {
                        for (int row = 1; row <= rows; row++)
                        {
                            for (int col = 1; col <= columns; col++)
                            {
                                excelSheet.AddValue(row, col, cells[row, col]);
                            }
                        }
                    }
                }
                Log.WriteLine(Enums.LogSeverityType.DBG, "ExcelDocument", "GetExcelSheetList: the size of sheetNames is {0} and size of ExcelSheet list is {1}.", m_sheetNames == null ? 0 : m_sheetNames.Length, excelSheetList.Count);
            }
            Close();
            return excelSheetList;
        }

        #endregion

        #region Private functions

        /// <summary>
        /// Open Excel Docuemnt
        /// </summary>
        /// <returns></returns>
        private bool Open()
        {
            bool result = true;
            try
            {
                m_excelApplication = new Microsoft.Office.Interop.Excel.Application(m_filePath);

                object objOpt = System.Reflection.Missing.Value;
                m_excelDoc = (Workbook)m_excelApplication.Workbooks.Open(m_filePath, objOpt, true, objOpt, objOpt, objOpt, false, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
                Log.WriteLine(Enums.LogSeverityType.DBG, "ExcelDocument", "Open: open excel {0} success.", m_filePath);
                result = true;
            }
            catch (Exception ex)
            {
                Close();
                Log.WriteLine(Enums.LogSeverityType.ERR, "ExcelDocument", "Open: there is an exception occurred when creating or opening Excel document.");
                Log.WriteLine(Enums.LogSeverityType.ERR, "ExcelDocument", "{0}", ex);
                result = false;
            }
            return result;
        }

        /// <summary>
        /// Close Excel Document
        /// </summary>
        private void Close()
        {
            try
            {
                if (m_excelDoc != null)
                    m_excelDoc.Close();
                if (m_excelApplication != null)
                    m_excelApplication.Quit();
                Log.WriteLine(Enums.LogSeverityType.DBG, "ExcelDocument", "Close: close excel {0} success.", m_filePath);
            }
            catch (Exception ex)
            {
                Log.WriteLine(Enums.LogSeverityType.ERR, "ExcelDocument", "Close: there is an exception occurred when quitting or closing Excel document.");
            }
        }

        #endregion
    }
}
